package com.ratingbay.console.naivequery;

import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.math.BigDecimal;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.EntityTransaction;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.GameSummary;

public class TweetLinkNaiveQuery{
	private TweetLinkNaiveQuery(){
	}
	private static TweetLinkNaiveQuery self = null;
    public static TweetLinkNaiveQuery getInstance(){
    	if(null == self){
    		self = new TweetLinkNaiveQuery();
    	}
        return self;
    }


    //for cjob
    public List<Long> getAllIdGames(){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "SELECT id FROM game";
        Query query =  em.createNativeQuery(sql);
        
        return query.getResultList();
    } 

    public int getTweetCount(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select tweet_count from link where id = " + linkId;
        Query query =  em.createNativeQuery(sql);

        String re = String.valueOf(query.getSingleResult());
        
        if(re == null || re.length() == 0||re.equals("null")){
        	return 0;
        }else{
        	int res = Integer.parseInt(re);
        	return res;
        }
    }

    public String getMediaDirectLink(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select media_direct_link from link where id = " + linkId;
        Query query =  em.createNativeQuery(sql);

        List<String> res = (List<String>) query.getResultList();
        if(res.size() > 0){
        	String l = res.get(0).replaceAll("'", "''");
        	return l;
        }
        return null;
    }

    public String getLink(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select link from link where id = " + linkId;
        Query query =  em.createNativeQuery(sql);

        List<String> res = (List<String>) query.getResultList();
        if(res.size() > 0){
        	String l = res.get(0).replaceAll("'", "''");
        	return l;
        }
        return null;
    }
    
    public Boolean isLinkId(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select count(*) from rb_link where id = " + linkId;
        Query query =  em.createNativeQuery(sql);
        
        List<Long> res = (List<Long>) query.getResultList();
        if(res.get(0)!=0){
        		return true;
        	}else{
        		return false;
        	}
    }
    
    public void insertLink(Long linkId,Long gameId,int type){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "insert into rb_link (id_game,link,type,media_direct_link,tweet_count,id) values ("+ gameId + ",'" + getLink(linkId) + "'," + type + ",'" + getMediaDirectLink(linkId) + "'," + getTweetCount(linkId) + "," + linkId + ")"; 
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public void updataLink(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "update rb_link set tweet_count = " + getTweetCount(linkId) + " where id = " + linkId;
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public void deleteLink(Long gameId,int type){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "delete from rb_link where id_game = " +gameId+ " and type = " + type + " and id not in(select id from rb_link where id_game = " + gameId + " and type = " + type + " order by tweet_count desc limit 500)" ;
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public Long countLink(Long gameId,int type){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "select count(*) from rb_link where id_game = " + gameId + " and type = " + type;
    	
    	Query query =  em.createNativeQuery(sql);
    	List<Long> res = (List<Long>) query.getResultList();
    	if(res.size() > 0){
        	return res.get(0);
        }
        return 0L;
    }
    
    //cjob entry
    public void analysisTweetAndLink(List<Long> idGames) {
        System.out.println("CJOB start analysisLink");

		EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        Query query =  null;
  
    	for(int i=0; i< idGames.size(); i++) {
    		for(int type =0;type<3;type++){
			Long gameId = idGames.get(i);
			String tSql = "select count(*) from rb_link where id_game = " + gameId + " and type = " + type;
			String qSql;
			if(type==0){
				qSql = "select id from link l where l.id in(select min(id) from (select * from link i where i.type = "+type+" and i.id_game = "+gameId+" order by tweet_count desc limit 1000) t where subString(t.media_direct_link,0,19) = 'http://www.youtube' group by t.media_direct_link order by max(t.tweet_count) desc) order by tweet_count desc limit 500";
			}else{
				qSql = "select id from link l where l.id in(select min(id) from (select * from link i where i.type = "+type+" and i.id_game = "+gameId+" order by tweet_count desc limit 1000) t where t.media_direct_link is not null group by t.media_direct_link order by max(t.tweet_count) desc) order by tweet_count desc limit 500";
			}
		    query = em.createNativeQuery(tSql);
			Long isInserted = (Long)query.getSingleResult();
			query = em.createNativeQuery(qSql);
			List<Long> tweetLink = query.getResultList();
			if(1 > isInserted){
				for(int j=0;j<tweetLink.size();j++){
					Long linkId = tweetLink.get(j);
					insertLink(linkId,gameId,type);
				}	
				}else{
					for(int j=0;j<tweetLink.size();j++){
						Long linkId = tweetLink.get(j);
						if(isLinkId(linkId)){
							updataLink(linkId);
						}else{
							insertLink(linkId,gameId,type);
						}
					}
					if(countLink(gameId,type)>500){
						deleteLink(gameId,type);
					}	
				}
    		}
		}
        
        System.out.println("CJOB end analysisLink");
    }
}